package com.kpmg.datalake.common.utils;


import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ExcelUtil {
    private ExcelUtil() {
		
	}
	
	// POI excel导入(xls)
	public static List<List<Object>> importExcelXLS(String file, int ignoreRows,int sheetIndex) throws IOException {

		List<List<Object>> result = new ArrayList<List<Object>>();

		InputStream infile = new FileInputStream(file);
		BufferedInputStream in = new BufferedInputStream(infile);
		POIFSFileSystem fs = new POIFSFileSystem(in);
		HSSFWorkbook wb = new HSSFWorkbook(fs);

		HSSFCell cell = null;
			// 取得工作簿
			HSSFSheet st = wb.getSheetAt(sheetIndex);
			// 标题行不取
			// 列长按照标题行的最后一行来获取
			HSSFRow lastHeaderRow = st.getRow(ignoreRows - 1);
		if (lastHeaderRow == null) {
			return result;
		}
			int headerColumnLen = lastHeaderRow.getLastCellNum()-1;
			for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
				HSSFRow row = st.getRow(rowIndex);
				if (row == null) {
					continue;
				}
				result = check(cell,st,rowIndex,headerColumnLen);
			}
		in.close();

		return result;
	}
	
	public static List<List<Object>> check(HSSFCell cell,HSSFSheet st,int rowIndex,int headerColumnLen) {
		List<List<Object>> result = new ArrayList<List<Object>>();
		HSSFRow row = st.getRow(rowIndex);
		List<Object> values = new ArrayList<Object>();
		boolean hasValue = false;
		for (int columnIndex = 0; columnIndex <= headerColumnLen; columnIndex++) {
			Object value = getData(row,cell,columnIndex);
			if (columnIndex == 0 && value == null) {
				break;
			}
			values.add(value);
			hasValue = true;
		}
		if (hasValue) {
			result.add(values);
		}
	return result;
	}
	public static Object getData(HSSFRow row,HSSFCell cell,int columnIndex) {
		Object value = null;
		// 获取单元格
		cell = row.getCell(columnIndex);
		if (cell != null) {
			value = checkData(cell);
		}
		return value;
	
	}
	
	public static Object checkData(HSSFCell cells) {
		Object values = null;
		switch (cells.getCellType()) {
		case HSSFCell.CELL_TYPE_STRING:
			values = cells.getStringCellValue();
			break;
		case HSSFCell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(cells)) {
				Date date = cells.getDateCellValue();
				values = date;
			} else {
				NumberFormat nft = NumberFormat.getInstance();
				nft.setGroupingUsed(false);
				double d = Double.parseDouble(nft.format(cells.getNumericCellValue()));
				if (d % 1.0 != 0) {
					nft.setMinimumFractionDigits(8);
				}
				values = nft.format(cells.getNumericCellValue());
			}
			break;
		case HSSFCell.CELL_TYPE_FORMULA:
			// 导入时如果为公式生成的数据则无值
			if (cells.getCachedFormulaResultType() == 0) {
				values = cells.getNumericCellValue();
			} else {
				values = cells.getRichStringCellValue();
			}
			break;
		case HSSFCell.CELL_TYPE_BLANK:
			break;
		case HSSFCell.CELL_TYPE_ERROR:
			values = "";
			break;
		case HSSFCell.CELL_TYPE_BOOLEAN:
			values = (cells.getBooleanCellValue() == true ? "Y" : "N");
			break;
		default:
		}
		return values;
	
	}
	

	// POI excel导入(xlsx)
	public static List<List<Object>> importExcelXLSX(File file, int ignoreRows,int sheetIndex) throws IOException {
		List<List<Object>> result = new ArrayList<List<Object>>();
		InputStream ism = new FileInputStream(file);
		XSSFWorkbook wb = new XSSFWorkbook(ism);
		XSSFCell cell = null;
			XSSFSheet st = (XSSFSheet) wb.getSheetAt(sheetIndex);
			
			XSSFRow lastHeaderRow = st.getRow(ignoreRows - 1);
		if (lastHeaderRow == null) {
			return result;
		}
			int headerColumnLen = lastHeaderRow.getLastCellNum()-1;
			for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {

				XSSFRow row = st.getRow(rowIndex);
				if (row == null) {
					continue;
				}
				List<Object> values = new ArrayList<Object>();
				boolean hasValues = false;

				for (int columnIndex = 0; columnIndex <= headerColumnLen; columnIndex++) {
					Object value = getDataXlxsRange( row, cell, columnIndex);
					if (columnIndex == 0 && value == null) {
						break;
					}
					values.add(value);
					hasValues = true;
				}
				if (hasValues) {
					result.add(values);
				}
			}
			ism.close();

		return result;
	}
	
	public static Object getDataXlxsRange(XSSFRow row,XSSFCell cell,int columnIndex) {
		Object value = null;
		cell = row.getCell(columnIndex);
		if (cell != null) {
			// 注意：一定要设成这个，否则可能会出现乱码
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_STRING:
				value = cell.getStringCellValue();
				break;
			case HSSFCell.CELL_TYPE_NUMERIC:
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					Date date = cell.getDateCellValue();
					value = date;
				} else {
					value = cell.getRawValue();
				}
				break;
			case HSSFCell.CELL_TYPE_FORMULA:
				// 导入时如果为公式生成的数据则无值
				if (cell.getCachedFormulaResultType() == 0) {
					value = cell.getNumericCellValue();
				} else {
					value = cell.getRichStringCellValue();
				}
				break;
			case HSSFCell.CELL_TYPE_BLANK:
				break;
			case HSSFCell.CELL_TYPE_ERROR:
				value = "";
				break;
			case HSSFCell.CELL_TYPE_BOOLEAN:
				value = (cell.getBooleanCellValue() == true ? "Y" : "N");
				break;
			default:
			}
		}
		return value;
	
	
	}

	
	    // POI excel导入(xlsx)
		public static List<List<Object>> importExcelXLSXRange(File file, int focusRow,int sheetIndex) throws IOException {
			List<List<Object>> result = new ArrayList<List<Object>>();
			InputStream is = new FileInputStream(file);
			XSSFWorkbook wb = new XSSFWorkbook(is);
			XSSFCell cell = null;
				XSSFSheet st = (XSSFSheet) wb.getSheetAt(sheetIndex);
				XSSFRow lastHeaderRow = st.getRow(focusRow);
			if (lastHeaderRow == null) {
				return result;
			}
				int headerColumnLen = lastHeaderRow.getLastCellNum() -1 ;

					XSSFRow row = st.getRow(focusRow);
					if (row == null) {
						return result;
					}
					List<Object> values = new ArrayList<Object>();
					boolean hasValue = false;

					for (int columnIndex = 0; columnIndex <= headerColumnLen; columnIndex++) {
						Object value = getDataXlxsRange(row,cell,columnIndex);
						if (columnIndex == 0 && value == null) {
							break;
						}
						values.add(value);
						hasValue = true;
					}
					if (hasValue) {
						result.add(values);
					}
			is.close();

			return result;
		}
		
}
